import { jest } from '@jest/globals';
import mysql from 'mysql2/promise';
import DatabaseService from '../../services/DatabaseService.js';
import { mockLogger, testUtils, testDbConfig } from '../setup.js';

// 模拟mysql2
jest.mock('mysql2/promise');
jest.mock('fs/promises');
jest.mock('path');

const createMockPool = () => ({
  getConnection: jest.fn(),
  execute: jest.fn(),
  end: jest.fn()
});

const createMockConnection = () => ({
  beginTransaction: jest.fn(),
  execute: jest.fn(),
  commit: jest.fn(),
  rollback: jest.fn(),
  release: jest.fn()
});

describe('DatabaseService', () => {
  let databaseService;
  let mockPool;
  let mockConnection;

  beforeEach(async () => {
    mockPool = createMockPool();
    mockConnection = createMockConnection();
    
    mysql.createPool.mockReturnValue(mockPool);
    mockPool.getConnection.mockResolvedValue(mockConnection);
    
    // 模拟fs和path
    const fs = await import('fs/promises');
    const path = await import('path');
    
    fs.readFile = jest.fn().mockResolvedValue('CREATE TABLE test (id INT);');
    path.resolve = jest.fn().mockReturnValue('/fake/path/init.sql');
    
    databaseService = new DatabaseService();
    await testUtils.waitFor(100); // 等待初始化完成
  });

  afterEach(() => {
    jest.clearAllMocks();
  });

  describe('initialization', () => {
    it('should initialize database pool successfully with retry mechanism', async () => {
      // 重新创建一个新的数据库服务实例
      const mockConnection = createMockConnection();
      mockConnection.ping = jest.fn().mockResolvedValue();
      
      mockPool.getConnection.mockResolvedValue(mockConnection);
      
      const newDbService = new DatabaseService();
      await testUtils.waitFor(200);
      
      expect(mysql.createPool).toHaveBeenCalledWith(expect.objectContaining({
        waitForConnections: true,
        connectionLimit: expect.any(Number),
        queueLimit: 0,
        acquireTimeout: 60000,
        timeout: 60000,
        reconnect: true
      }));
      
      expect(mockConnection.ping).toHaveBeenCalled();
    });

    it('should retry connection on failure', async () => {
      const mockConnection = createMockConnection();
      mockConnection.ping = jest.fn()
        .mockRejectedValueOnce(new Error('Connection failed'))
        .mockRejectedValueOnce(new Error('Connection failed again'))
        .mockResolvedValue(); // 第三次成功
      
      mockPool.getConnection.mockResolvedValue(mockConnection);
      
      const newDbService = new DatabaseService();
      await testUtils.waitFor(300);
      
      expect(mockConnection.ping).toHaveBeenCalledTimes(3);
    });

    it('should handle initialization errors and cleanup pool', async () => {
      const mockConnection = createMockConnection();
      mockConnection.ping = jest.fn().mockRejectedValue(new Error('Connection failed'));
      
      mockPool.getConnection.mockResolvedValue(mockConnection);
      mockPool.end.mockResolvedValue();
      
      await expect(async () => {
        const db = new DatabaseService();
        await testUtils.waitFor(200);
      }).rejects.toThrow('数据库初始化失败');
      
      expect(mockPool.end).toHaveBeenCalled();
    });

    it('should check connection status', async () => {
      const mockConnection = createMockConnection();
      mockConnection.ping = jest.fn().mockResolvedValue();
      
      mockPool.getConnection.mockResolvedValue(mockConnection);
      
      const result = await databaseService.checkConnection();
      
      expect(result).toBe(true);
      expect(mockConnection.ping).toHaveBeenCalled();
      expect(mockConnection.release).toHaveBeenCalled();
    });

    it('should return false on connection check failure', async () => {
      mockPool.getConnection.mockRejectedValue(new Error('No connection'));
      
      const result = await databaseService.checkConnection();
      
      expect(result).toBe(false);
    });
  });

  describe('createRoom', () => {
    const roomData = {
      id: 'TEST01',
      name: 'Test Room',
      hostId: 'host-123',
      hostName: 'TestHost',
      playerCount: 1,
      maxPlayers: 3,
      gameConfig: { gameMode: 'standard' }
    };

    it('should create room successfully', async () => {
      mockConnection.execute.mockResolvedValue([[], {}]);

      const result = await databaseService.createRoom(roomData);

      expect(result).toBe(true);
      expect(mockConnection.beginTransaction).toHaveBeenCalled();
      expect(mockConnection.execute).toHaveBeenCalledTimes(2); // 房间和玩家插入
      expect(mockConnection.commit).toHaveBeenCalled();
      expect(mockConnection.release).toHaveBeenCalled();
    });

    it('should handle database transaction errors with proper rollback', async () => {
      const dbError = new Error('Insert failed');
      dbError.code = 'ER_DUP_ENTRY';
      dbError.sqlState = '23000';
      
      mockConnection.execute.mockRejectedValue(dbError);
      mockConnection.rollback.mockResolvedValue();

      await expect(databaseService.createRoom(roomData)).rejects.toThrow('房间创建失败: 数据重复');
      expect(mockConnection.rollback).toHaveBeenCalled();
      expect(mockConnection.release).toHaveBeenCalled();
    });

    it('should handle rollback errors gracefully', async () => {
      mockConnection.execute.mockRejectedValue(new Error('Insert failed'));
      mockConnection.rollback.mockRejectedValue(new Error('Rollback failed'));

      await expect(databaseService.createRoom(roomData)).rejects.toThrow();
      expect(mockConnection.rollback).toHaveBeenCalled();
      expect(mockConnection.release).toHaveBeenCalled();
    });

    it('should handle connection pool not initialized', async () => {
      const dbService = new DatabaseService();
      dbService.pool = null;

      await expect(dbService.createRoom(roomData)).rejects.toThrow('数据库连接池未初始化');
    });

    it('should handle connection errors gracefully', async () => {
      mockPool.getConnection.mockRejectedValue(new Error('No connection'));

      await expect(databaseService.createRoom(roomData)).rejects.toThrow();
    });
  });

  describe('getRoomById', () => {
    const mockRoom = {
      id: 'TEST01',
      name: 'Test Room',
      status: 'waiting'
    };

    const mockPlayers = [
      {
        player_id: 'player-1',
        player_name: 'Player1',
        seat_index: 0,
        is_ready: false,
        status: 'online',
        socket_id: 'socket-1',
        joined_at: new Date()
      }
    ];

    it('should get room with players successfully', async () => {
      mockPool.execute
        .mockResolvedValueOnce([[mockRoom], {}])  // 房间查询
        .mockResolvedValueOnce([mockPlayers, {}]); // 玩家查询

      const result = await databaseService.getRoomById('TEST01');

      expect(result).toEqual(expect.objectContaining({
        id: 'TEST01',
        name: 'Test Room',
        players: expect.arrayContaining([
          expect.objectContaining({
            id: 'player-1',
            name: 'Player1',
            seatIndex: 0,
            isReady: false
          })
        ])
      }));
    });

    it('should return null for non-existent room', async () => {
      mockPool.execute.mockResolvedValue([[], {}]);

      const result = await databaseService.getRoomById('INVALID');

      expect(result).toBeNull();
    });

    it('should handle database errors', async () => {
      mockPool.execute.mockRejectedValue(new Error('Query failed'));

      await expect(databaseService.getRoomById('TEST01')).rejects.toThrow();
    });
  });

  describe('joinRoom', () => {
    const mockRoom = {
      id: 'TEST01',
      max_players: 3,
      status: 'waiting'
    };

    beforeEach(() => {
      mockConnection.execute
        .mockResolvedValueOnce([[mockRoom], {}])  // 房间状态查询
        .mockResolvedValueOnce([[], {}])          // 玩家存在检查
        .mockResolvedValueOnce([[{ count: 1 }], {}]) // 玩家计数
        .mockResolvedValueOnce([[], {}])          // 座位查询
        .mockResolvedValueOnce([[], {}])          // 插入玩家
        .mockResolvedValueOnce([[], {}]);         // 更新房间玩家数
    });

    it('should join room successfully', async () => {
      const result = await databaseService.joinRoom('TEST01', 'player-123', 'NewPlayer');

      expect(result).toBe(true);
      expect(mockConnection.beginTransaction).toHaveBeenCalled();
      expect(mockConnection.commit).toHaveBeenCalled();
    });

    it('should reject joining non-existent room', async () => {
      mockConnection.execute.mockResolvedValueOnce([[], {}]); // 空房间结果

      await expect(
        databaseService.joinRoom('INVALID', 'player-123', 'NewPlayer')
      ).rejects.toThrow('房间不存在或已开始游戏');
    });

    it('should reject joining full room', async () => {
      mockConnection.execute
        .mockResolvedValueOnce([[mockRoom], {}])
        .mockResolvedValueOnce([[], {}])
        .mockResolvedValueOnce([[{ count: 3 }], {}]); // 房间已满

      await expect(
        databaseService.joinRoom('TEST01', 'player-123', 'NewPlayer')
      ).rejects.toThrow('房间已满');
    });

    it('should handle existing player reconnection', async () => {
      mockConnection.execute
        .mockResolvedValueOnce([[mockRoom], {}])
        .mockResolvedValueOnce([[{ player_id: 'player-123' }], {}]) // 玩家已存在
        .mockResolvedValueOnce([[], {}])     // 更新玩家状态
        .mockResolvedValueOnce([[], {}]);    // 更新房间玩家数

      const result = await databaseService.joinRoom('TEST01', 'player-123', 'ExistingPlayer');

      expect(result).toBe(true);
      expect(mockConnection.commit).toHaveBeenCalled();
    });
  });

  describe('leaveRoom', () => {
    it('should leave room and delete empty room', async () => {
      mockConnection.execute
        .mockResolvedValueOnce([[], {}])              // 删除玩家
        .mockResolvedValueOnce([[{ count: 0 }], {}])  // 玩家计数为0
        .mockResolvedValueOnce([[], {}]);             // 删除房间

      const result = await databaseService.leaveRoom('player-123', 'TEST01');

      expect(result).toBe(0);
      expect(mockConnection.beginTransaction).toHaveBeenCalled();
      expect(mockConnection.commit).toHaveBeenCalled();
    });

    it('should leave room and transfer host', async () => {
      mockConnection.execute
        .mockResolvedValueOnce([[], {}])              // 删除玩家
        .mockResolvedValueOnce([[{ count: 2 }], {}])  // 还有2个玩家
        .mockResolvedValueOnce([[], {}])              // 更新玩家数量
        .mockResolvedValueOnce([[{ host_id: 'player-123' }], {}])  // 获取房间信息
        .mockResolvedValueOnce([[{ player_id: 'new-host' }], {}])  // 获取新房主
        .mockResolvedValueOnce([[], {}]);             // 更新房主

      const result = await databaseService.leaveRoom('player-123', 'TEST01');

      expect(result).toBe(2);
      expect(mockConnection.execute).toHaveBeenCalledWith(
        expect.stringContaining('UPDATE game_rooms SET host_id'),
        expect.arrayContaining(['new-host', 'TEST01'])
      );
    });

    it('should handle database errors with rollback', async () => {
      mockConnection.execute.mockRejectedValue(new Error('Delete failed'));

      await expect(
        databaseService.leaveRoom('player-123', 'TEST01')
      ).rejects.toThrow();
      
      expect(mockConnection.rollback).toHaveBeenCalled();
    });
  });

  describe('updatePlayerStatus', () => {
    it('should update player status successfully', async () => {
      mockPool.execute.mockResolvedValue([[], {}]);

      await databaseService.updatePlayerStatus('player-123', 'TEST01', 'offline', 'socket-456');

      expect(mockPool.execute).toHaveBeenCalledWith(
        expect.stringContaining('UPDATE room_players SET status'),
        expect.arrayContaining(['offline', 'socket-456', 'player-123', 'TEST01'])
      );
    });

    it('should update status without socket ID', async () => {
      mockPool.execute.mockResolvedValue([[], {}]);

      await databaseService.updatePlayerStatus('player-123', 'TEST01', 'offline');

      expect(mockPool.execute).toHaveBeenCalledWith(
        expect.not.stringContaining('socket_id'),
        expect.arrayContaining(['offline', 'player-123', 'TEST01'])
      );
    });

    it('should handle update errors gracefully', async () => {
      mockPool.execute.mockRejectedValue(new Error('Update failed'));

      await expect(
        databaseService.updatePlayerStatus('player-123', 'TEST01', 'offline')
      ).rejects.toThrow();
    });
  });

  describe('createGameSession', () => {
    const sessionData = {
      id: 'session-123',
      roomId: 'TEST01',
      gameState: { round: 1 },
      currentPlayerIndex: 0,
      currentRound: 1
    };

    it('should create game session successfully', async () => {
      mockPool.execute.mockResolvedValue([[], {}]);

      const result = await databaseService.createGameSession(sessionData);

      expect(result).toBe(true);
      expect(mockPool.execute).toHaveBeenCalledWith(
        expect.stringContaining('INSERT INTO game_sessions'),
        expect.arrayContaining([
          'session-123',
          'TEST01',
          JSON.stringify(sessionData.gameState),
          0,
          1,
          'playing'
        ])
      );
    });

    it('should handle creation errors', async () => {
      mockPool.execute.mockRejectedValue(new Error('Insert failed'));

      await expect(databaseService.createGameSession(sessionData)).rejects.toThrow();
    });
  });

  describe('updateGameSession', () => {
    const updateData = {
      gameState: { round: 2 },
      currentPlayerIndex: 1,
      currentRound: 2,
      roundCards: [],
      collectedCards: {}
    };

    it('should update game session successfully', async () => {
      mockPool.execute.mockResolvedValue([[], {}]);

      await databaseService.updateGameSession('session-123', updateData);

      expect(mockPool.execute).toHaveBeenCalledWith(
        expect.stringContaining('UPDATE game_sessions'),
        expect.arrayContaining([
          JSON.stringify(updateData.gameState),
          1,
          2,
          JSON.stringify(updateData.roundCards),
          JSON.stringify(updateData.collectedCards),
          'session-123'
        ])
      );
    });

    it('should handle null values in update data', async () => {
      const partialUpdate = {
        gameState: { round: 2 },
        currentPlayerIndex: 1,
        currentRound: 2,
        roundCards: null,
        collectedCards: null
      };

      mockPool.execute.mockResolvedValue([[], {}]);

      await databaseService.updateGameSession('session-123', partialUpdate);

      const lastCall = mockPool.execute.mock.calls[mockPool.execute.mock.calls.length - 1];
      expect(lastCall[1]).toContain(null);
    });
  });

  describe('recordGameAction', () => {
    const actionData = {
      sessionId: 'session-123',
      playerId: 'player-123',
      actionType: 'play_cards',
      actionData: { cards: [] },
      roundNumber: 1,
      sequence: 1
    };

    it('should record game action successfully', async () => {
      mockPool.execute.mockResolvedValue([[], {}]);

      await databaseService.recordGameAction(actionData);

      expect(mockPool.execute).toHaveBeenCalledWith(
        expect.stringContaining('INSERT INTO game_actions'),
        expect.arrayContaining([
          'session-123',
          'player-123',
          'play_cards',
          JSON.stringify(actionData.actionData),
          1,
          1
        ])
      );
    });

    it('should not throw errors on action recording failure', async () => {
      mockPool.execute.mockRejectedValue(new Error('Record failed'));

      // 应该不抛出错误，只记录日志
      await expect(databaseService.recordGameAction(actionData)).resolves.toBeUndefined();
    });
  });

  describe('cleanupExpiredData', () => {
    it('should cleanup expired data successfully', async () => {
      mockConnection.execute
        .mockResolvedValueOnce([[], {}])  // 清理会话
        .mockResolvedValueOnce([[], {}])  // 清理房间
        .mockResolvedValueOnce([[], {}]); // 更新离线玩家

      await databaseService.cleanupExpiredData();

      expect(mockConnection.beginTransaction).toHaveBeenCalled();
      expect(mockConnection.commit).toHaveBeenCalled();
      expect(mockConnection.execute).toHaveBeenCalledTimes(3);
    });

    it('should handle cleanup errors gracefully', async () => {
      mockConnection.execute.mockRejectedValue(new Error('Cleanup failed'));

      // 应该不抛出错误
      await expect(databaseService.cleanupExpiredData()).resolves.toBeUndefined();
    });
  });

  describe('getSafeErrorMessage', () => {
    it('should map known database error codes', () => {
      const testCases = [
        { code: 'ER_DUP_ENTRY', expected: '数据重复' },
        { code: 'ER_NO_REFERENCED_ROW_2', expected: '关联数据不存在' },
        { code: 'ER_DATA_TOO_LONG', expected: '数据长度超限' },
        { code: 'ECONNREFUSED', expected: '数据库连接被拒绝' },
        { code: 'ETIMEDOUT', expected: '数据库连接超时' }
      ];

      for (const testCase of testCases) {
        const error = new Error('Test error');
        error.code = testCase.code;
        const result = databaseService.getSafeErrorMessage(error);
        expect(result).toBe(testCase.expected);
      }
    });

    it('should filter sensitive information from messages', () => {
      const error = new Error('Access denied for user \'testuser\'@\'localhost\' using password YES');
      const result = databaseService.getSafeErrorMessage(error);
      expect(result).toContain('[password]');
      expect(result).toContain('[user]');
    });

    it('should return generic message for unknown errors', () => {
      const error = new Error();
      const result = databaseService.getSafeErrorMessage(error);
      expect(result).toBe('数据库操作失败');
    });
  });

  describe('close', () => {
    it('should close database pool successfully', async () => {
      mockPool.end.mockResolvedValue();

      await databaseService.close();

      expect(mockPool.end).toHaveBeenCalled();
      expect(databaseService.pool).toBeNull();
    });

    it('should handle close errors gracefully', async () => {
      mockPool.end.mockRejectedValue(new Error('Close failed'));

      await databaseService.close();

      expect(databaseService.pool).toBeNull();
    });

    it('should handle multiple close calls safely', async () => {
      mockPool.end.mockResolvedValue();

      await databaseService.close();
      await databaseService.close(); // 第二次调用不应该报错

      expect(mockPool.end).toHaveBeenCalledTimes(1);
    });
  });

  describe('getAvailableRooms', () => {
    it('should get available rooms with player count', async () => {
      const mockRooms = [
        {
          id: 'TEST01',
          name: 'Room 1',
          status: 'waiting',
          max_players: 3,
          created_at: new Date(),
          actual_player_count: 2
        }
      ];

      mockPool.execute.mockResolvedValue([mockRooms, {}]);

      const result = await databaseService.getAvailableRooms(10);

      expect(result).toEqual([
        expect.objectContaining({
          id: 'TEST01',
          name: 'Room 1',
          playerCount: 2,
          maxPlayers: 3
        })
      ]);
    });

    it('should handle empty rooms list', async () => {
      mockPool.execute.mockResolvedValue([[], {}]);

      const result = await databaseService.getAvailableRooms();

      expect(result).toEqual([]);
    });
  });
});